package top.jlzhou.tools.office.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * <pre>xlsx实现</pre>
 * @ClassName	top.jlzhou.tools.office.excel.Excel2007
 * @author	J.L.Zhou
 * @date	2019年3月9日 下午6:58:09
 * @version 	V1.0
 * @Copyright	2019 jlzhou.top Inc. All rights reserved. 
 * 	注意：本内容仅限于公司内部传阅，禁止外泄以及用于其他的商业目
 */
public class Excel2007 extends top.jlzhou.tools.office.Excel {
	
	private XSSFWorkbook workbook;
	private FormulaEvaluator evaluator;
	
	public Excel2007(InputStream in) throws Exception{
		workbook = new XSSFWorkbook(in);
		evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		in.close();
	}

	@Override
	public Workbook getWorkbook() {
		return workbook;
	}
	@Override
	public String[] listSheet() {
		List<String> list = new ArrayList<String>();
		XSSFSheet sheet;
		int i=0;
		try{
			while((sheet  = workbook.getSheetAt(i++)) != null){
				list.add(sheet.getSheetName());
			}
		}catch(Exception ex){}
		return list.toArray(new String[0]);
	}


	@SuppressWarnings("deprecation")
	@Override
	public String readOne(int sheetIndex, int r, int c1) {
		try{
			XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
			XSSFCell c = sheet.getRow(r).getCell(c1);
			
			if(c.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
				return Double.toString(c.getNumericCellValue());
			}else if(c.getCellType() == XSSFCell.CELL_TYPE_STRING){
				return c.getStringCellValue().trim();
			}else if(c.getCellType() == XSSFCell.CELL_TYPE_FORMULA){
				evaluator.evaluateFormulaCell(c);
				return Double.toString(evaluator.evaluate(c).getNumberValue());
			}else if(c.getCellType() == XSSFCell.CELL_TYPE_ERROR){
				return Byte.toString(c.getErrorCellValue());
			}else if(c.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){
				return Boolean.toString(c.getBooleanCellValue());
			}else{
				return null;
			}
		}catch(Exception ex){
			return null;
		}
	}

	@SuppressWarnings("deprecation")
	@Override
	public List<List<String>> read(int sheetIndex, int startRow, int endRow) {
		List<List<String>> list = new ArrayList<List<String>>();
		XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
		XSSFRow row= null;
		while((row = sheet.getRow(startRow++))!=null){
			if(startRow-1>endRow){
				break;
			}
			List<String> r = new ArrayList<String>();
			
			int i=0;
			XSSFCell c =  null ;
			while((c = row.getCell(i))!=null){
				++i;
				try{
					if(c.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
						r.add(Double.toString(c.getNumericCellValue()));
					}else if(c.getCellType() == XSSFCell.CELL_TYPE_STRING){
						r.add(c.getStringCellValue().trim());
					}else if(c.getCellType() == XSSFCell.CELL_TYPE_FORMULA){
						evaluator.evaluateFormulaCell(c);
						r.add(Double.toString(evaluator.evaluate(c).getNumberValue()));
					}else if(c.getCellType() == XSSFCell.CELL_TYPE_ERROR){
						r.add(Byte.toString(c.getErrorCellValue()));
					}else if(c.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){
						r.add(Boolean.toString(c.getBooleanCellValue()));
					}else{
						r.add(null);
					}
				}catch(Exception ex){
					r.add(null);
				}
			}
			if(r.size()>0){
				list.add(r);
			}
		}
		return list;
	}



	@SuppressWarnings("deprecation")
	@Override
	public void reload(int sheetIndex) {
		int r = 0;
		XSSFRow row= null;
		XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
		while((row = sheet.getRow(r++))!=null){
			int c = 0;
			XSSFCell cell =  null ;
			while((cell = row.getCell(c++))!=null){
				if(cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA){
					evaluator.evaluateFormulaCell(cell);
				}
			}
		}
	}


	@Override
	public void save(File file) throws Exception {
		workbook.write(new FileOutputStream(file));

	}

	@Override
	public void save(OutputStream out)  throws Exception{
		workbook.write(out);
	}
	

	


	@Override
	public void write(int sheetIndex, int rowIndex, int colIndex, boolean value)
			throws Exception {
		getCell(sheetIndex, rowIndex, colIndex).setCellValue(value);
	}

	@Override
	public void write(int sheetIndex, int rowIndex, int colIndex, Date value)
			throws Exception {
		getCell(sheetIndex, rowIndex, colIndex).setCellValue(value);
	}

	@Override
	public void write(int sheetIndex, int rowIndex, int colIndex, String value)
			throws Exception {
		getCell(sheetIndex, rowIndex, colIndex).setCellValue(value);
	}

	@Override
	public void write(int sheetIndex, int rowIndex, int colIndex, double value)
			throws Exception {
		getCell(sheetIndex, rowIndex, colIndex).setCellValue(value);
	}

	


}
